
'------------------------------------------
' Hands-On 27-1
'------------------------------------------

Sub FollowMe()
    Dim myRange As Range
    Set myRange = Sheets(1).Range("A1")

    myRange.Hyperlinks.Add Anchor:=myRange, _
       Address:="http://search.yahoo.com/", _
       ScreenTip:="Search Yahoo", _
       TextToDisplay:="Click here"
End Sub


'------------------------------------------
' Hands-On 27-2 - Code in Sheet2
'------------------------------------------


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
            Cancel As Boolean)
    Dim strSearch As String

    strSearch = Sheets(2).Range("C3").Formula
    If Target = Range("C3") Then
        Cancel = True
        ActiveWorkbook.FollowHyperlink _
        Address:="http://www.altavista.com/cgi-bin/query", _
        ExtraInfo:="q=" & strSearch, _
        Method:=msoMethodGet
    End If
End Sub


'------------------------------------------
' Hands-On 27-3 - Code in the PublishExample.xlsm file
'------------------------------------------

' The procedure below will publish a worksheet
' with an embedded chart as static HTML

Sub PublishOnWeb(strSheetName As String, _
                 strFileName As String)

    Dim objPub As Excel.PublishObject
    Set objPub = ThisWorkbook.PublishObjects.Add( _
       SourceType:=xlSourceSheet, _
       Filename:=strFileName, Sheet:=strSheetName, _
       HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
    objPub.Publish True
End Sub

Sub CreateHTMLFile()
    Call PublishOnWeb("Help Desk", _
         "C:\Ex07_ByExample\WorksheetWithChart.htm")
End Sub


'------------------------------------------
' Hands-On 27-4 - Code in the MyWebQueries.xlsm file
'------------------------------------------


Sub NewBooks_Wordware()
    ' create a web query in the current worksheet
    ' connect to the web, retrieve data, and paste it
    ' in the worksheet as static text

  With ActiveSheet.QueryTables.Add _
       (Connection:="URL;http://www.wordware.com/computer/whats_new.shtml", _
         Destination:=Range("A1"))
         .BackgroundQuery = True
         .WebSelectionType = xlSpecifiedTables
         .WebTables = "7"
         .WebFormatting = xlWebFormattingNone
         .Refresh BackgroundQuery:=False
         .SaveData = True
  End With
End Sub

'------------------------------------------
' Hands-On 27-5
' Please follow the instructions in the book.
'------------------------------------------


'------------------------------------------
' Hands-On 27-6
'------------------------------------------

Sub Portfolio()
   Dim sht As Worksheet
   Dim qryTbl As QueryTable

   ' insert a new worksheet in the current workbook
   Set sht = ThisWorkbook.Worksheets.Add
   ' create a new web query in a worksheet
   Set qryTbl = sht.QueryTables.Add(Connection:="URL;http://moneycentral." & _
   "msn.com/investor/external/excel/quotes.asp?SYMBOL=GOOG&SYMBOL=YHOO", _
   Destination:=sht.Range("A1"))
   ' retrieve data from web page and specify formatting
   ' paste data in a worksheet
   With qryTbl
       .BackgroundQuery = True
       .WebSelectionType = xlSpecifiedTables
       .WebTables = "1"
       .WebFormatting = xlWebFormattingAll
       .Refresh BackgroundQuery:=False
       .SaveData = True
   End With
   ' delete unwanted rows/columns
   With sht
       .Rows("2").Delete
       .Columns("B:C").Delete
       .Rows("5:16").Delete
   End With
End Sub


'------------------------------------------
' Hands-On 27-7
'------------------------------------------

Sub Portfolio2()
   Dim sht As Worksheet
   Dim qryTbl As QueryTable

   ' insert a new worksheet in the current workbook
   Set sht = ThisWorkbook.Worksheets.Add
   ' create a new web query in a worksheet
   Set qryTbl = sht.QueryTables.Add(Connection:="URL;http://moneycentral." & _
   "msn.com/investor/external/excel/quotes.asp?SYMBOL=[""Enter " & "symbols separated by spaces""]", _
   Destination:=sht.Range("A1"))
   ' retrieve data from web page and specify formatting
   ' paste data in a worksheet
   With qryTbl
       .BackgroundQuery = True
       .WebSelectionType = xlSpecifiedTables
       .WebTables = "1"
       .WebFormatting = xlWebFormattingAll
       .Refresh BackgroundQuery:=False
       .SaveData = True
   End With
   ' delete unwanted rows/columns
   With sht
       .Rows("2").Delete
       .Rows("6:18").Delete
       .Columns("B:C").Delete
  End With
End Sub


'------------------------------------------
' Hands-On 27-8
'------------------------------------------

Sub Currency_Exchange_POST()
    Dim sht As Worksheet

   ' insert a new worksheet in the current workbook
   Set sht = ThisWorkbook.Worksheets.Add
  With ActiveSheet.QueryTables.Add(Connection:= _
       "URL;http://www.xe.NET/cgi-bin/ucc/convert", _
        Destination:=Range("B1"))
    .PostText = "From=[""Enter the currency symbol from which " & _
      "you want to convert""]&Amount=[""Enter amount you wish " & _
      "to convert""]&To=[""Enter the currency symbol you want " & _
      "to obtain""]"
    .BackgroundQuery = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "11"

    .WebFormatting = xlWebFormattingAll
    .RefreshStyle = xlOverwriteCells
    .AdjustColumnWidth = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
  End With
End Sub




